"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : 二级分区
Case Name   : list_list二级分区表:备机只读/主机copy
Create At   : 2022/2/11
Owner       : @daiguatutu
Description :
    1.主机执行-创建表空间和二级分区表,并插入数据
    2.备机执行增删改查不同的操作
    3.清空数据,从标准输入copy数据到表,查询数据
    4.数据库初始用户执行copy to/from,查询数据
    5.清理环境
Expect      :
    1.主机执行成功
    2.备机执行失败
    3.成功,2条数据
    4.成功,4条数据
    5.清理成功
History     :
    Modified by @daiguatutu 2022/6/9: 增加主备同步，保证新建表信息同步至备机
"""
import os
import re
import unittest
from yat.test import Node
from yat.test import macro

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

COMMONSH = CommonSH("PrimaryDbUser")


@unittest.skipIf(1 == COMMONSH.get_node_num(), "单机不执行")
class Subpartition(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.primaryNode = Node('PrimaryDbUser')
        self.standby_sh = CommonSH('Standby1DbUser')
        self.constant = Constant()
        self.t_name = 't_subpartition_0064'
        self.ts_name = 'ts_subpartition_0064'
        self.copy_file = os.path.join(macro.DB_INSTANCE_PATH,
                                      'dir_subpartition_0064.dat')

    def test_subpartition(self):
        text = '-----step1:主机执行-创建表空间和二级分区表,并插入数据 expect:成功-----'
        self.log.info(text)
        create_sql = f"drop table if exists {self.t_name};" \
            f"drop tablespace if exists {self.ts_name};" \
            f"create tablespace {self.ts_name} relative location " \
            f"'{self.ts_name}';" \
            f"create table if not exists {self.t_name}(" \
            f"col_1 int ,col_2 int ,col_3 varchar2 ( 30 ) " \
            f"not null ,col_4 int)tablespace {self.ts_name} " \
            f"partition by list (col_1) subpartition by list (col_2)(" \
            f"partition p_list_1 values(-1,-2,-3,-4,-5,-6,-7,-8,-9,-10 )(" \
            f"subpartition p_list_1_1 values " \
            f"(0,-1,-2,-3,-4,-5,-6,-7,-8,-9 )," \
            f"subpartition p_list_1_2 values (default ))," \
            f"partition p_list_2 values(0,1,2,3,4,5,6,7,8,9)(" \
            f"subpartition p_list_2_1 values (0,1,2,3,4,5,6,7,8,9 )," \
            f"subpartition p_list_2_2 values (default )," \
            f"subpartition p_list_2_3 values " \
            f"(10,11,12,13,14,15,16,17,18,19)," \
            f"subpartition p_list_2_4 values " \
            f"(20,21,22,23,24,25,26,27,28,29)," \
            f"subpartition p_list_2_5 values " \
            f"(30,31,32,33,34,35,36,37,38,39))," \
            f"partition p_list_3 values(10,11,12,13,14,15,16,17,18,19)(" \
            f"subpartition p_list_3_2 values (default ))," \
            f"partition p_list_4 values(default )," \
            f"partition p_list_5 values(20,21,22,23,24,25,26,27,28,29)(" \
            f"subpartition p_list_5_1 values (0,1,2,3,4,5,6,7,8,9 )," \
            f"subpartition p_list_5_2 values (default )," \
            f"subpartition p_list_5_3 values " \
            f"(10,11,12,13,14,15,16,17,18,19)," \
            f"subpartition p_list_5_4 values " \
            f"(20,21,22,23,24,25,26,27,28,29)," \
            f"subpartition p_list_5_5 values " \
            f"(30,31,32,33,34,35,36,37,38,39))," \
            f"partition p_list_6 values(30,31,32,33,34,35,36,37,38,39)," \
            f"partition p_list_7 values(40,41,42,43,44,45,46,47,48,49)(" \
            f"subpartition p_list_7_1 values ( default ))) " \
            f"enable row movement;" \
            f"insert into {self.t_name} values (generate_series(0, 19)," \
            f"generate_series(0, 1000),generate_series(0, 99));"
        msg = self.pri_sh.execut_db_sql(create_sql)
        self.log.info(msg)
        self.assertIn(self.constant.TABLESPCE_CREATE_SUCCESS, msg,
                      '执行失败:' + text)
        self.assertIn(self.constant.TABLESPCE_DROP_SUCCESS, msg,
                      '执行失败:' + text)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, msg, '执行失败:' + text)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, msg, '执行失败:' + text)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, msg, '执行失败:' + text)

        text = '-----校验备机是否完成数据同步-----'
        self.log.info(text)
        check_msg = self.standby_sh.check_location_consistency(
            'standby', COMMONSH.get_node_num())
        self.assertTrue(check_msg, '执行失败:' + text)

        text = '-----step2:备机执行增删改查不同的操作 expect:失败-----'
        self.log.info(text)
        sql_2 = f"update {self.t_name} set col_2=8 where col_2=5; " \
            f"delete {self.t_name} where col_2=8; " \
            f"insert into  {self.t_name} values (generate_series(0, 19)," \
            f"generate_series(0, 1000),generate_series(0, 99)); " \
            f"create  index on {self.t_name}(col_1,col_2) local; " \
            f"create view tv_subpartition as select * from {self.t_name}; " \
            f"alter table {self.t_name} rename  to list_list_03;"
        msg = self.standby_sh.execut_db_sql(sql_2)
        self.log.info(msg)
        self.assertEqual(6, len(
            re.findall(self.constant.read_only_error_msg, msg)),
                         '执行失败:' + text)

        text = '-----step3:清空数据,从标准输入copy数据到表,查询数据, expect:成功,2条数据-----'
        self.log.info(text)
        connect_sql = f'gsql -d {self.primaryNode.db_name} ' \
            f'-p {self.primaryNode.db_port}'
        sql_3 = f"truncate {self.t_name}; " \
            f"copy {self.t_name} from  stdin; " \
            f"select * from {self.t_name} subpartition(p_list_2_1); "
        execute_cmd = f'''source {macro.DB_ENV_PATH}
                          expect <<EOF
                          set timeout 60
                          spawn {connect_sql}
                          expect "{self.primaryNode.db_name}=#"
                          send "{sql_3}\\n"
                          expect ">>"
                          send "1\\t1\\t1\\t1\\n"
                          expect ">>"
                          send "2\\t2\\t2\\t2\\n"
                          expect ">>"
                          send "\\\\\\\\.\\n"
                          expect "{self.primaryNode.db_name}=#"
                          send "\\\\\\\\q\\n"
                          expect eof\n''' + '''EOF'''
        self.log.info(execute_cmd)
        msg = self.primaryNode.sh(execute_cmd).result()
        self.log.info(msg)
        self.assertIn(self.constant.TRUNCATE_SUCCESS_MSG, msg, '执行失败:' + text)
        self.assertIn('2 rows', msg, '执行失败:' + text)

        text = '-----step4:数据库初始用户执行copy to/from,查询数据; expect:成功,4条数据-----'
        self.log.info(text)
        sql_5 = f"copy {self.t_name} to '{self.copy_file}'; " \
            f"copy {self.t_name} from '{self.copy_file}'; " \
            f"select * from {self.t_name}; "
        msg = self.pri_sh.execut_db_sql(sql_5)
        self.log.info(msg)
        self.assertIn('COPY 2', msg, '执行失败:' + text)
        self.assertIn('4 rows', msg, '执行失败:' + text)

    def tearDown(self):
        self.log.info('-----step5:清理环境-----')
        text_1 = '-----删除表和表空间 expect:成功-----'
        self.log.info(text_1)
        del_cmd = f'drop table if exists {self.t_name};' \
            f'drop tablespace if exists {self.ts_name};'
        msg = self.pri_sh.execut_db_sql(del_cmd)
        self.log.info(msg)

        text_2 = '-----删除copy文件 expect:成功-----'
        self.log.info(text_2)
        del_cmd = f'rm -rf {self.copy_file}'
        del_msg = self.primaryNode.sh(del_cmd).result()
        self.log.info(del_msg)

        self.assertIn(self.constant.TABLESPCE_DROP_SUCCESS, msg,
                      '执行失败:' + text_1)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, msg, '执行失败:' + text_1)
        self.assertEqual('', del_msg, '执行失败:' + text_2)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
